<?php
class Salle extends Model {
	
	private static $table_name = 'SALLE';
	
	public function __construct($salleData) {
		parent::__construct();
		$this->values = $salleData;
	}
	
	// Récupère la salle $id
	public static function getSalle($id) {
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("SELECT * FROM ".self::$table_name." WHERE IDSALLE=:id");
		$requete->execute(array('id' => $id));
		$tab = $requete->fetch(PDO::FETCH_ASSOC);
		return new Salle($tab);
	}
	
	public static function getAllSalles($userId) {
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("	SELECT * FROM ".self::$table_name."
											ORDER BY DATECREATION DESC");
		$requete->execute();
		$i=0;
		$data = array();
		while($tab = $requete->fetch(PDO::FETCH_ASSOC)) {
			$salle = new Salle($tab);
			$groupes = $salle->getGroupes();
			if($salle->accesSalle($userId) || empty($groupes) || $userId==__ID_ADMIN) {
				$data[$i]= $salle;
				$i++;
			}
		}
		return $data;
	}
	
	public static function getTopSalles() {
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("	SELECT IDSALLE, SUM( NOMBRELIKES )
											FROM MESSAGE
											WHERE (TO_DAYS(NOW())-7 < TO_DAYS( MESSAGE.DATEPOST ))
											GROUP BY IDSALLE
											ORDER BY SUM( NOMBRELIKES ) DESC
											LIMIT 10
										"); 
		$requete->execute(array());
		$data=array();
		$i=0;
		//$data = $requete->fetch(PDO::FETCH_ASSOC);
		while($tab = $requete->fetch(PDO::FETCH_ASSOC)) {
			$data[$i]=Salle::getSalle($tab['IDSALLE']);
			$i++;
		}
		return $data;
	}
	
	// Récupère toutes les salles de l'utilisateur $id (renvoie un tableau de salle)
	public static function getSallesOfUser($id) {
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("SELECT * FROM ".self::$table_name." WHERE IDELEVE=:id");
		$requete->execute(array('id' => $id));
		$i=0;
		$data = array();
		while($tab = $requete->fetch(PDO::FETCH_ASSOC)) {
			$data[$i]=new Salle($tab);
			$i++;
		}
		return $data;
	}
	
	// Récupère les salles du thème $id_theme
	public static function getSallesWithTheme($id_theme) {
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("SELECT * FROM ".self::$table_name." 
										LEFT JOIN A_POUR_THEME ON ".self::$table_name.".IDSALLE = A_POUR_THEME.IDSALLE
										WHERE A_POUR_THEME.IDTHEME=:id");
		$requete->execute(array('id' => $id_theme));
		$i=0;
		$data = array();
		while($tab = $requete->fetch(PDO::FETCH_ASSOC)) {
			$data[$i]=new Salle($tab);
			$i++;
		}
		return $data;
	}
	
	// Récupère les 10 dernières salles créées
	public static function getLastSalles($userId) {
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("SELECT * FROM ".self::$table_name." 
										ORDER BY DATECREATION DESC
										LIMIT 10");
		$requete->execute();
		$i=0;
		$data = array();
		while($tab = $requete->fetch(PDO::FETCH_ASSOC)) {
			$salle = new Salle($tab);
			$groupes = $salle->getGroupes();
			if($salle->accesSalle($userId) || empty($groupes) || $userId==__ID_ADMIN) {
				$data[$i]= $salle;
				$i++;
			}
		}
		return $data;
	}
	
	public static function getLastPublicSalles() {
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("	SELECT * FROM ".self::$table_name."
											WHERE IDSALLE 
											NOT IN (SELECT IDSALLE FROM EST_INVITE)
											ORDER BY DATECREATION DESC
											LIMIT 10");
		$requete->execute();
		$i=0;
		$data = array();
		while($tab = $requete->fetch(PDO::FETCH_ASSOC)) {
			$data[$i]=new Salle($tab);
			$i++;
		}
		return $data;
	}
	
	// Récupère le thème de la salle
	public function getTheme() {
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("	SELECT THEME.NOMTHEME, THEME.IDTHEME
											FROM THEME
											INNER JOIN A_POUR_THEME ON THEME.IDTHEME = A_POUR_THEME.IDTHEME
											WHERE A_POUR_THEME.IDSALLE =:id"
										);	
		$requete->execute(array('id'=>$this->getId()));
		$i=0;
		$data = array();
		Loader::loadModel('Theme');
		while($tab = $requete->fetch(PDO::FETCH_ASSOC)) {
			$data[$i]=new Theme($tab);
			$i++;
		}
		return $data;
	}
	//Recupère les messages de la salle
	public function getMessages(){
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("	SELECT *
											FROM MESSAGE
											WHERE IDSALLE =:id
											AND MES_IDMESSAGE IS NULL
											ORDER BY DATEPOST ASC"
										);	
		$requete->execute(array('id'=>$this->getId()));
		$i=0;
		$data = array();
		Loader::loadModel('Message');
		while($tab = $requete->fetch(PDO::FETCH_ASSOC)) {
			$data[$i]['message']=new Message($tab);
			$data[$i]['comments']=$data[$i]['message']->getComments();
			$i++;
		}
		return $data;
	}
	
	public function getLastMessage() {
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("	SELECT *
											FROM MESSAGE
											WHERE MESSAGE.IDSALLE =:id
											AND MES_IDMESSAGE IS NULL
											ORDER BY DATEPOST DESC
											LIMIT 1"
										);	
		$requete->execute(array('id'=>$this->getId()));
		$data = $requete->fetch(PDO::FETCH_ASSOC);
		Loader::loadModel('Message');
		return new Message($data);
		
	}
	
	// Récupère la date et l'heure du message le plus récent de la salle
	public function getDateLastPost(){
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("	SELECT DATEPOST
											FROM MESSAGE
											WHERE MESSAGE.IDSALLE = ':id'"
										);	
		$requete->execute(array('id'=>$this->getId()));
		$data = $requete->fetch(PDO::FETCH_BOTH);
		return $data;
	}
	
	public function getGroupes() {
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("	SELECT *
											FROM GROUPE
											LEFT JOIN EST_INVITE ON EST_INVITE.IDGROUPE = GROUPE.IDGROUPE
											WHERE EST_INVITE.IDSALLE =:id
											"
										);	
		$requete->execute(array('id'=>$this->getId()));
		$i=0;
		$data = array();
		Loader::loadModel('Groupe');
		while($tab = $requete->fetch(PDO::FETCH_ASSOC)) {
			$data[$i]=new Groupe($tab);
			$i++;
		}
		return $data;
	}
	
	// Retourne vrai si l'utilisateur peut accèder à la salle
	public function accesSalle($idUser){
		$dbConnection = PDOConnection::getInstance();
		$requete=$dbConnection->prepare("	SELECT SALLE.IDSALLE
											FROM SALLE
											LEFT JOIN EST_INVITE ON SALLE.IDSALLE = EST_INVITE.IDSALLE
											LEFT JOIN APPARTIENT_A ON EST_INVITE.IDGROUPE = APPARTIENT_A.IDGROUPE
											LEFT JOIN ELEVE ON APPARTIENT_A.IDELEVE = ELEVE.IDELEVE
											WHERE ELEVE.IDELEVE=:idUser");
		$requete->execute(array('idUser'=>$idUser));
		$tab = $requete->fetchAll(PDO::FETCH_BOTH);
		foreach($tab as $valeur) {
			if ($valeur['IDSALLE'] == $this->getId()) { 
   				return true;
			}
		}
		return false;
	}
	
	public function getId() { return $this->values['IDSALLE'];}
	public function getCreateurId() { return $this->values['IDELEVE'];}
	public function getNom() { return $this->values['NOMSALLE'];}
	public function getCreationDate(){ return $this->values['DATECREATION'];}
	public function getFermetureDate() { return $this->values['DATEFERMETURE'];}
	
	public function getAuthor() {
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$requete=$dbConnection->prepare("	SELECT PSEUDO
											FROM ELEVE
											WHERE ELEVE.IDELEVE = :id"
										);	
		$requete->execute(array('id'=>$this->getCreateurId()));
		$data = $requete->fetch(PDO::FETCH_ASSOC);
		return $data;
	}

	// Supprimer une salle
	public function supprimerSalle(){
		$dbConnection = PDOConnection::getInstance();
		$requete=$dbConnection->prepare("	DELETE FROM MESSAGE
											WHERE IDSALLE = :idsalle;
										");
		$requete->execute(array('idsalle'=>$this->getId()));
		
		$requete=$dbConnection->prepare("	DELETE FROM EST_INVITE
											WHERE IDSALLE = :idsalle
										");
		$requete->execute(array('idsalle'=>$this->getId()));
		
		$requete=$dbConnection->prepare("	DELETE FROM A_POUR_THEME
											WHERE IDSALLE = :idsalle;
										");
		$requete->execute(array('idsalle'=>$this->getId()));
		
		$requete=$dbConnection->prepare("	DELETE FROM SALLE
											WHERE IDSALLE = :idsalle;
										");	
		$requete->execute(array('idsalle'=>$this->getId()));
	}
	
	// Modifier le nom d'une salle
	public function modifierTitreSalle($nom){
		$dbConnection = PDOConnection::getInstance();
		$requete=$dbConnection->prepare("	UPDATE SALLE
											SET NOMSALLE = :nom
											WHERE IDSALLE = :id
										");
		$requete->execute(array('nom'=>$nom,'id'=>$this->getId()));
	}
	
	// Inviter des groupes à la salle. Je pars du principe qu'il faudra utiliser plutôt cette fonction pour créer une salle
	// Genre créer une salle avec createSalle, en virant la partie "EST_INVITE" qu'il y a pour l'instant, et de lancer celle ci dessous
	// dans une "super-fonction", qui lance les deux quand un utilisateur crée une salle. J'sais pas si c'est bon^
	public function inviter($groupes){
		$dbConnection = PDOConnection::getInstance();
		$requete= $dbConnection->query("SET NAMES UTF8;");
		$delete=$dbConnection->prepare("	DELETE FROM EST_INVITE
											WHERE IDSALLE=:idsalle;
											");
		$delete->execute(array('idsalle'=>$this->getId()));
		
		$requete=$dbConnection->prepare("	INSERT INTO EST_INVITE(IDGROUPE,IDSALLE)
											VALUES (:idgroupe,:idsalle);
											");
		$message = 'Vous avez été conviés à la salle de discussion '.$this->getNom().'.';
		foreach($groupes as $groupe){
			$requete->execute(array('idsalle'=>$this->getId(),'idgroupe'=>$groupe->getGroupeId()));
			Loader::loadModel('Groupe');
			$membres = $groupe->getMembresGroupe();
			Loader::loadModel('MessagePrives');
			foreach($membres as $membre){
				MessagePrives::createMessagePrive(1,$membre->getId(),$message);
			}
		}
	}
	
	public function attribuerTheme($themes){
		$dbConnection = PDOConnection::getInstance();
		
		$delete = $dbConnection->prepare("	DELETE FROM A_POUR_THEME
											WHERE IDSALLE=:idsalle
											");
		$delete->execute(array('idsalle'=>$this->getId()));
		
		$lien = $dbConnection->prepare("	INSERT INTO A_POUR_THEME
												VALUES(:idsalle,:idtheme)
											");
		foreach($themes as $theme) {
			$lien->execute(array('idsalle'=>$this->getId(), 'idtheme'=>$theme));
		}
	}
	// Pour les stats
	public function getNumberOfSalles(){
		$dbConnection = PDOConnection::getInstance();
		$requete=$dbConnection->prepare("	SELECT COUNT(IDSALLE)
											FROM SALLE
										");
		$requete->execute();
		$data = $requete->fetch(PDO::FETCH_ASSOC);
		return $data['COUNT(IDSALLE)'];
	}
	public function getNumberOfPrivateSalles(){
		$dbConnection = PDOConnection::getInstance();
		$requete=$dbConnection->prepare("	SELECT COUNT(DISTINCT(IDSALLE))
											FROM EST_INVITE
										");
		$requete->execute();
		$data = $requete->fetch(PDO::FETCH_ASSOC);
		return $data['COUNT(DISTINCT(IDSALLE))'];
	}
	public function getNumberOfPublicSalles(){
		$dbConnection = PDOConnection::getInstance();
		$requete=$dbConnection->prepare("	SELECT COUNT(DISTINCT(IDSALLE))
											FROM SALLE
											WHERE IDSALLE NOT IN(SELECT DISTINCT(IDSALLE) FROM EST_INVITE)
										");
		$requete->execute();
		$data = $requete->fetch(PDO::FETCH_ASSOC);
		return $data['COUNT(DISTINCT(IDSALLE))'];
	}
}